import os,shutil
import mysql.connector
from datetime import datetime


mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="xiaocheng"
)
mycursor = mydb.cursor()

# 获取文件夹下所有文件名
def get_all_names(directory):
    filenames = []
    for root, dirs, files in os.walk(directory):
        for file in files:
            name=os.path.splitext(file)[0]
            filenames.append(name)
    return filenames


files=get_all_names("D:/Desk/精画图同步/去标注")


names = []
mycursor.execute("select operater_num from data_choose")
myresult = mycursor.fetchall()
for x in myresult:
    names.append(x[0])


datas=[]     
for file in files:
    name=file.split('&&')[0]
    if name in names:
        temp_list=[name,]
        mycursor.execute("select connect_id,pic,t_create from data_choose where operater_num=%s order by id asc limit 1",temp_list)
        myresult = mycursor.fetchone()
        dict=[]
        dict.append(myresult[0])
        dict.append('choose/'+myresult[1])
        dict.append(myresult[2])
        dict.append(file+'.png')
        dict.append('小成')
        dict.append('精画')
        datas.append(tuple(dict))
        path_old='D:/Desk/精画图同步/去标注/'+file+'.png'
        path_new='D:/Desk/精画图同步/精画/'+file+'.png'
        shutil.move(path_old,path_new)
        # break
        
print(len(datas))

# 插入数据
sql = "insert into task_draw (connect_id,refer_pic,t_create,draw_pic,belong,sort) values (%s,%s,%s,%s,%s,%s)" 
mycursor.executemany(sql, datas) 
mydb.commit()    # 数据表内容有更新，必须使用到该语句
 
print(mycursor.rowcount, "记录插入成功。")

mycursor.close()
mydb.close()